﻿using System;
using System.Collections.Generic;
using System.Reflection;
using System.Text;
using System.IO;
using System.Web;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System.Data;
using System.Data.OleDb;

namespace Common.Util
{
    public class ImportFromExcel
    {
        /// <summary>
        /// Import excel to list
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="stream">FileStream</param>
        /// <param name="list">Return list</param>
        /// <param name="fileName">Filename</param>
        /// <param name="session">Current Session of user</param>
        /// <param name="sheetName">Name of sheet</param>
        public static void Import<T>(Stream stream, List<T> list, string fileName, string session, string sheetName)
            where T : class, new()
        {
            // If list is null => declare new
            if (list == null)
                list = new List<T>();

            // If stream is null => return
            if (stream == null) return;

            // If Excel file is 2007 version, convert to 2003 version
            if (fileName.ToLower().EndsWith("xlsx"))
                stream = ConvertXlsxWorksheetToXlsWorksheet(stream, session, sheetName);

            var templateWorkbook = new HSSFWorkbook(stream, true);
            ISheet infoSheet = templateWorkbook.GetSheet(sheetName);
            if (infoSheet == null)
                return;

            PropertyInfo[] props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
            for (int i = 1; i < infoSheet.LastRowNum; i++)
            {
                IRow row = infoSheet.GetRow(i);
                var item = new T();

                int col = 0;
                foreach (PropertyInfo prop in props)
                {
                    // If current property is not string, skip it
                    if (prop.PropertyType != Type.GetType("System.String"))
                        continue;

                    ICell cell = row.GetCell(col);
                    string strValue = cell == null ? string.Empty : cell.StringCellValue;
                    prop.SetValue(item, strValue, null);
                    col++;
                }

                list.Add(item);
            }
        }

        /// <summary>
        /// Render a Excel 2007 (xlsx) Worksheet to NPOI Excel 2003 Worksheet, all excel formatting
        /// from XLSX will be lost when converted.  NPOI roadmap shows v1.6 will support Excel 2007 (xlsx).
        /// NPOI Roadmap  : http://npoi.codeplex.com/wikipage?title=NPOI%20Road%20Map&referringTitle=Documentation
        /// NPOI Homepage : http://npoi.codeplex.com/
        /// </summary>
        /// <param name="excelFileStream">XLSX FileStream</param>
        /// <param name="tmpFileName">FileName to create temp file</param>
        /// <param name="sheetName">Excel worksheet to convert</param>
        /// <returns>MemoryStream containing NPOI Excel workbook</returns>
        public static Stream ConvertXlsxWorksheetToXlsWorksheet(Stream excelFileStream, string tmpFileName, string sheetName)
        {
            // Temp directory
            string strTmpDir = HttpContext.Current.Server.MapPath("~/tmp");
            if (!Directory.Exists(strTmpDir)) Directory.CreateDirectory(strTmpDir);

            // Temp file name
            string tempFile = HttpContext.Current.Server.MapPath("~/tmp/" + tmpFileName + ".tmp");

            // Temp data container (using DataTable to leverage existing RenderDataTableToExcel function)
            var dt = new DataTable();

            try
            {
                // Create temp XLSX file
                var fileStream = new FileStream(tempFile, FileMode.Create, FileAccess.Write);

                const int length = 256;
                var buffer = new Byte[length];
                int bytesRead = excelFileStream.Read(buffer, 0, length);

                while (bytesRead > 0)
                {
                    fileStream.Write(buffer, 0, bytesRead);
                    bytesRead = excelFileStream.Read(buffer, 0, length);
                }

                excelFileStream.Close();
                fileStream.Close();

                // Read temp XLSX file using OLEDB
                // Tested on Vista & Windows 2008 R2
                using (var con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0; Extended Properties=Excel 12.0;Data Source=" + tempFile + @";Extended Properties=""Excel 12.0;HDR=YES;"""))
                {
                    con.Open();
                    string sql = String.Format("SELECT * FROM [{0}$]", sheetName);
                    var dbCommand = new OleDbCommand(sql, con);
                    var da = new OleDbDataAdapter(dbCommand);

                    da.Fill(dt);
                }
            }
            finally
            {
                // Make sure temp file is deleted
                File.Delete(tempFile);
            }

            // Return a new POI Excel 2003 Workbook
            return RenderDataTableToExcel(dt);
        }

        /// <summary>
        /// Render DataTable to NPOI Excel 2003 MemoryStream
        /// NOTE:  Limitation of 65,536 rows suppored by XLS
        /// </summary>
        /// <param name="sourceTable">Source DataTable</param>
        /// <returns>MemoryStream containing NPOI Excel workbook</returns>
        public static Stream RenderDataTableToExcel(DataTable sourceTable)
        {
            var workbook = new HSSFWorkbook();
            var memoryStream = new MemoryStream();
            // By default NPOI creates "Sheet0" which is inconsistent with Excel using "Sheet1"
            var sheet = (HSSFSheet)workbook.CreateSheet("Sheet1");
            var headerRow = (HSSFRow)sheet.CreateRow(0);

            // Header Row
            foreach (DataColumn column in sourceTable.Columns)
                headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);

            // Detail Rows
            int rowIndex = 1;

            foreach (DataRow row in sourceTable.Rows)
            {
                var dataRow = (HSSFRow)sheet.CreateRow(rowIndex);

                foreach (DataColumn column in sourceTable.Columns)
                {
                    dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
                }

                rowIndex++;
            }

            workbook.Write(memoryStream);
            memoryStream.Flush();
            memoryStream.Position = 0;

            return memoryStream;
        }

    }
}
